Generating Excel with Watermarks using .NET

Sample Project

Executable sample for this article: CloudyWing/ExcelWatermarkSample.

Please note that there is a significant time gap between the sample and this article, so keep the following in mind:

• The execution environment is .NET 10 using NPOI 2.8.0; the NPOI version used when writing this article was older.
• Image generation has been switched to SkiaSharp to replace System.Drawing.Common (as cross-platform support for this package is limited in newer .NET versions).
• The sample only demonstrates the NPOI approach and does not cover the EPPlus section of this article.

Differences in code structure are expected; use this article to understand the principles and the sample to obtain code that runs directly in a .NET 10 environment.

Introduction

I have a package under development called SpreadsheetExporter that can generate Excel files with watermarks using NPOI or EPPlus. However, since the watermark feature relies on System.Drawing.Common, and support for System.Drawing.Common began to decline with .NET 6, I initially considered restricting this feature to frameworks that support System.Drawing.Common. I eventually abandoned that idea and removed the feature entirely. To avoid losing the reference for future needs, I decided to document the method here.

Generating Excel with Watermarks

Excel does not have a built-in watermark feature, but you can simulate one by setting a full-page transparent background image.

Excel has three view modes: "Normal," "Page Break Preview," and "Page Layout," plus printing. Currently, I have not found an effective method for "Page Break Preview." For the others, you can use the following settings:

• Setting an Image in the Background displays the background image in "Normal" and "Page Layout" modes.
• Setting an Image in the Header displays the background image in "Page Layout" mode and when printing.

Generating Full-Page Images

Excel's page setup allows you to configure page orientation and size, which determines the dimensions of the image to be generated.

You can use the following code to retrieve a collection of PaperSize objects that contain width and height records. Default Excel paper sizes can all be found here.

PrinterSettings settings = new PrinterSettings() {
    PrinterName = "Microsoft XPS Document Writer"
};
foreach (System.Drawing.Printing.PaperSize printerPaperSize in settings.PaperSizes) {
    // printerPaperSize.RawKind serial number
    // printerPaperSize.PaperName e.g., A4
    // printerPaperSize.Width width
    // printerPaperSize.Height height
}

Below is the data for various PaperSize options:

RawKind	PaperName	Width	Height
1	Letter	850	1100
2	Letter Small	850	1100
3	Tabloid	1100	1700
4	Ledger	1700	1100
5	Legal	850	1400
6	Statement	550	850
7	Executive	725	1050
8	A3	1169	1654
9	A4	827	1169
10	A4 Small	827	1169
11	A5	583	827
12	B4 (JIS)	1012	1433
13	B5 (JIS)	717	1012
14	Folio	850	1300
15	Quarto	846	1083
16	10×14	1000	1400
17	11×17	1100	1700
18	Note	850	1100
19	Envelope #9	387	887
20	Envelope #10	412	950
21	Envelope #11	450	1037
22	Envelope #12	475	1100
23	Envelope #14	500	1150
24	C size sheet	1700	2200
25	D size sheet	2200	3400
26	E size sheet	3400	4400
27	Envelope DL	433	866
28	Envelope C5	638	902
29	Envelope C3	1276	1803
30	Envelope C4	902	1276
31	Envelope C6	449	638
32	Envelope C65	449	902
33	Envelope B4	984	1390
34	Envelope B5	693	984
35	Envelope B6	693	492
36	Envelope	433	906
37	Envelope Monarch	387	750
38	6 3/4 Envelope	362	650
39	US Std Fanfold	1487	1100
40	German Std Fanfold	850	1200
41	German Legal Fanfold	850	1300
42	B4 (ISO)	984	1390
43	Japanese Postcard	394	583
44	9×11	900	1100
45	10×11	1000	1100
46	15×11	1500	1100
47	Envelope Invite	866	866
50	Letter Extra	950	1200
51	Legal Extra	950	1500
53	A4 Extra	927	1269
54	Letter Transverse	850	1100
55	A4 Transverse	827	1169
56	Letter Extra Transverse	950	1200
57	Super A	894	1402
58	Super B	1201	1917
59	Letter Plus	850	1269
60	A4 Plus	827	1299
61	A5 Transverse	583	827
62	B5 (JIS) Transverse	717	1012
63	A3 Extra	1268	1752
64	A5 Extra	685	925
65	B5 (ISO) Extra	791	1087
66	A2	1654	2339
67	A3 Transverse	1169	1654
68	A3 Extra Transverse	1268	1752

Once you know the PaperSize dimensions, you can use the following code to adjust the blank areas of the watermark image background. Note that if the PaperSize is landscape, you must swap the width and height parameters.

public Image ResizeImageBackgroundToFullPage(Image watermark, int width, int height){
    if (watermark.Width > width || watermark.Height > height) {
        using (Image image = ZoomOutImage(width, height)) {
            return ResizeImageBackgroundToFullPageInternal(width, height, image);
        }
    }

    return ResizeImageBackgroundToFullPageInternal(width, height, watermark);
}

private Image ZoomOutImage(int pageWidth, int pageHeight) {
    decimal scale = Math.Max((decimal)watermark.Width / pageWidth, (decimal)watermark.Height / pageHeight);
    return new Bitmap(watermark, (int)(watermark.Width / scale), (int)(watermark.Height / scale));
}

private Image ResizeImageBackgroundToFullPageInternal(int pageWidth, int pageHeight, Image image) {
    Image bitmap = new Bitmap(pageWidth, pageHeight);
    using Graphics graphics = Graphics.FromImage(bitmap);
    graphics.Clear(Color.White);
    graphics.DrawImage(image, (pageWidth - image.Width) / 2, (pageHeight - image.Height) / 2);
    graphics.CompositingQuality = CompositingQuality.HighQuality;
    graphics.SmoothingMode = SmoothingMode.HighQuality;
    graphics.Save();

    return bitmap;
}

If you need to generate text images programmatically, you can use the following code:

public Image DrawText(string text, Font font, Color textColor, Color backColor, int width, int height) {
    // Create a Bitmap object with specified width and height as an image container
    Image img = new Bitmap(width, height);
    using (Graphics drawing = Graphics.FromImage(img)) {

        // Calculate coordinates of the text within the image container
        SizeF textSize = drawing.MeasureString(text, font, 0, StringFormat.GenericTypographic);
        float x = (width - textSize.Width) / 2;
        float y = (height - textSize.Height) / 2;

        drawing.TranslateTransform(x + (textSize.Width / 2), y + (textSize.Height / 2));
        // Rotate the graphic 45 degrees counter-clockwise
        drawing.RotateTransform(-45);
        drawing.TranslateTransform(-(x + (textSize.Width / 2)), -(y + (textSize.Height / 2)));

        // Clear a rectangle on the image container and fill it with the background color
        drawing.Clear(backColor);

        // Create a solid brush for drawing text
        Brush textBrush = new SolidBrush(textColor);
        drawing.DrawString(text, font, textBrush, x, y);

        drawing.Save();
        return img;
    }
}

TIP

For the logic regarding image rotation, you can refer to "C# Using GDI+ to Implement Text with Center Rotation (Any Angle)".

Generating Excel with Watermarks using EPPlus

Below is sample code for generating an Excel file with a watermark using EPPlus, where the watermark type is Image.

sheet.HeaderFooter.OddHeader.InsertPicture(watermark, PictureAlignment.Centered);
sheet.BackgroundImage.Image = watermark;

WARNING

This approach may not be applicable to EPPlus 6. Due to issues with System.Drawing.Common support in .NET 6 and later, EPPlus 6 removed the dependency on System.Drawing.Common. I have not followed the details, so I am unsure of the adjustment method.

Generating Excel with Watermarks using NPOI (XLSX)

To my knowledge, the current NPOI API does not allow direct setting of Background Images and Header Images, but you can handle this using lower-level APIs provided by NPOI.

To generate an Excel file with a watermark, you must first understand the XML structure generated when setting Background and Header Images. Below is an excerpt of the relevant XML:

Sheet XML content, where rId1 and rId2 are defined in \xl\worksheets_rels\{Sheet Name}.xml.rels.

<!-- Header Image -->
<headerFooter><oddHeader><![CDATA[&C&G]]></oddHeader></headerFooter><legacyDrawingHF r:id="rId2"/>
<!-- Background Image -->
<picture r:id="rId1"></picture>

{Sheet Name}.xml.rels

<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image1.png" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing1.vml" /></Relationships>

vmlDrawing1.vml uses o:relid="rId1" to associate the image rId1, with the definition located in \xl\drawings\_rels\vmlDrawing1.vml.rels.

<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
  <o:shapelayout v:ext="edit">
    <o:idmap v:ext="edit" data="1" />
  </o:shapelayout>
  <v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe">
    <v:stroke joinstyle="miter" />
    <v:path gradientshapeok="t" o:connecttype="rect" />
  </v:shapetype>
  <v:shape id="CH" type="#_x0000_t75" style="position:absolute;margin-left:0;margin-top:0;width:876.75pt;height:620.25pt;z-index:1">
    <v:imagedata o:relid="rId1" o:title="" />
    <o:lock v:ext="edit" rotation="t" />
  </v:shape>
</xml>

The content of vmlDrawing1.vml.rels is as follows:

<?xml version="1.0" encoding="utf-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image1.png" />
</Relationships>

Knowing this information, one could piece together the code to set the watermark, but a problem arises: the built-in XSSFVMLDrawing is used for creating Comment objects, so the generated structure differs from what is required and must be defined manually.

private class VmlRelation : POIXMLRelation {
    private static readonly Lazy<VmlRelation> instance = new(() => {
        return new VmlRelation(
                "application/vnd.openxmlformats-officedocument.vmlDrawing",
                "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing",
                "/xl/drawings/vmlDrawing#.vml",
                typeof(VmlDrawing)
        );
    });

    private VmlRelation(string type, string rel, string defaultName, Type cls) : base(type, rel, defaultName, cls) { }

    public static VmlRelation Instance => instance.Value;
}

private class VmlDrawing : POIXMLDocumentPart {
    public string PictureRelId { get; set; }

    public Image Image { get; set; }

    protected override void Commit() {
        PackagePart part = GetPackagePart();
        Stream @out = part.GetOutputStream();
        Write(@out);
        @out.Close();
    }

    private void Write(Stream stream) {
        // Pixel => Points
        float width = Image.Width * 72 / Image.HorizontalResolution;
        float height = Image.Height * 72 / Image.VerticalResolution;

        using StreamWriter sw = new(stream);
        XmlDocument doc = new();
        doc.LoadXml($@"
<xml xmlns:v=""urn:schemas-microsoft-com:vml"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"">
<o:shapelayout v:ext=""edit"">
<o:idmap v:ext=""edit"" data=""1"" />
</o:shapelayout>
<v:shapetype id=""_x0000_t202"" coordsize=""21600,21600"" o:spt=""202"" path=""m,l,21600r21600,l21600,xe"">
<v:stroke joinstyle=""miter"" />
<v:path gradientshapeok=""t"" o:connecttype=""rect"" />
</v:shapetype>
<v:shape id=""CH"" type=""#_x0000_t75"" style=""position:absolute;margin-left:0;margin-top:0;width:{width}pt;height:{height}pt;z-index:1"">
<v:imagedata o:relid=""{PictureRelId}"" o:title="""" />
<o:lock v:ext=""edit"" rotation=""t"" />
</v:shape>
</xml>");

        doc.Save(stream);
    }
}

Finally, we can use the following code to set the watermark.

MemoryStream imageMs = new MemoryStream();
watermark.Save(imageMs, System.Drawing.Imaging.ImageFormat.Png);

int pictureIdx = workbook.AddPicture(imageMs.ToArray(), PictureType.PNG);
POIXMLDocumentPart docPart = workbook.GetAllPictures()[pictureIdx] as POIXMLDocumentPart;

POIXMLDocumentPart.RelationPart backgroundRelPart = sheet.AddRelation(null, XSSFRelation.IMAGES, docPart);

sheet.GetCTWorksheet().picture = new CT_SheetBackgroundPicture {
    id = backgroundRelPart.Relationship.Id
};

int drawingNumber = (sheet.Workbook as XSSFWorkbook)
    .GetPackagePart()
    .Package
    .GetPartsByContentType(XSSFRelation.VML_DRAWINGS.ContentType).Count + 1;
VmlDrawing drawing = (VmlDrawing)sheet.CreateRelationship(VmlRelation.Instance, XSSFFactory.GetInstance(), drawingNumber);

POIXMLDocumentPart.RelationPart headerRelPart = drawing.AddRelation(null, XSSFRelation.IMAGES, docPart);

drawing.Image = watermark;
drawing.PictureRelId = headerRelPart.Relationship.Id;

sheet.Header.Center = HeaderFooter.PICTURE_FIELD.sequence;
sheet.GetCTWorksheet().legacyDrawingHF = new CT_LegacyDrawing {
    id = sheet.GetRelationId(drawing)
};

Change Log

• 2023-02-24 Initial version of the document created.
• 2026-05-17 Added link to the GitHub sample project.